﻿using log4net;
using SISCON.Common;
using SISCON.Dao;
using SISCON.Util;
using SISCON.Model;
using SISCON.Vo;
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;

namespace SISCON.Bl
{
    public class UploadBL
    {
        private static ILog _Logger = LogManager.GetLogger(typeof(UploadBL));
        private UserBL _UserBL = new UserBL();
        
        private TeacherDAO _TeacherDAO = new TeacherDAO();
        private ClassroomDAO _ClassroomDAO = new ClassroomDAO();
        private CourseDAO _CourseDAO = new CourseDAO();
        private ScheduleDAO _ScheduleDAO = new ScheduleDAO();
        private UploadDAO _UploadDAO = new UploadDAO();

        private delegate List<Teacher> QueryCourseTeachers(DataRow row);
        private delegate List<Schedule> QueryCourseSchedules(DataRow row);

        /// <summary>
        /// /*Valida los usuarios que tienen acceso al metodo UploadExcelFile y pobla la base de datos a partir de un archivo
        /// Excel (.xls, .xlsx)*/
        /// </summary>
        /// <param name="sessionUUID">Sesion del sistema</param>
        /// <param name="base64EncodedFile">Nombre codificado en base 64 del archivo</param>
        /// <param name="fileName">Nombre del archivo</param>
        public void UploadExcelFile(string sessionUUID, string base64EncodedFile, string fileName)
        {
            try
            {
                long userId = _UserBL.RequireRole(sessionUUID, Role.WRITE_ACCESS, Role.UPLOADS);

                //Convert, validate and save excel file
                byte[] fileBytes = base64EncodedFile != null ? Convert.FromBase64String(base64EncodedFile) : new byte[0];
                string filePath = null;
                string extension = fileName.Substring(fileName.LastIndexOf('.') + 1);

                if (extension != "xls" && extension != "xlsx")
                {
                    throw new SISCONException("Este no es un archivo de Excel válido.");
                }

                if (fileBytes.LongLength == 0)
                {
                    throw new SISCONException("El archivo está vacío.");
                }

                filePath = SaveUtil.SaveFile(fileBytes, fileName, null);

                //Get worksheet name
                /*
                theWorkbook = excelObj.Workbooks.Open(filePath, Type.Missing, true);

                foreach (Worksheet ws in theWorkbook.Worksheets)
                {
                    worksheet = ws;
                    break;
                }
                */
                string strWorksheetName = ListSheetInExcel(filePath)[0];//Get the name of worksheet.

                //Load excel worksheet to queryable table
                string connectionString = extension == "xlsx" ?
                            "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;"
                           : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Excel 8.0;HDR=YES;IMEX=0";

                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [" + strWorksheetName + "]", connectionString);
                DataSet ds = new DataSet();

                adapter.Fill(ds, fileName);

                var table = ds.Tables[fileName].AsEnumerable();

                 _Logger.Info("TABLA EN MEMORIA");

                //Lambda function: Get all course schedules
                QueryCourseSchedules qcs = (row) =>
                {
                    List<Schedule> schedules = new List<Schedule>();
                    string[] days = {"Lu", "Ma", "Mi", "Ju", "Vi", "Sa", "Do" };

                    for (int i = 1; i <= int.Parse(row.Field<double>("# Horarios").ToString()); i++)
                    {
                        Schedule schedule = new Schedule();
                        schedule.Classroom = new Classroom();

                        if (row.Field<string>("Estado") != null && row.Field<string>("Estado").ToUpper() == "ACTIVO" && row.Field<string>("Clave" + i) != "Sin horario")
                        {
                            schedule.Days = new List<string>();

                            foreach (string day in days)
                            {
                                if (row.Field<string>(day + i) != null && row.Field<string>(day + i).Trim() != String.Empty)
                                {
                                    schedule.Days.Add(row.Field<string>(day + i));
                                }
                            }

                            double startDouble;
                            double endDouble;

                            if (row.Field<object>("Hora Inicio" + i) != null && row.Field<object>("Hora Fin" + i) != null)
                            {
                                if (double.TryParse(row.Field<object>("Hora Inicio" + i).ToString(), out startDouble) && double.TryParse(row.Field<object>("Hora Fin" + i).ToString(), out endDouble))
                                {
                                    schedule.StartsAt = DateTime.FromOADate(startDouble);
                                    schedule.EndsAt = DateTime.FromOADate(endDouble);
                                }

                                DateTime startDate;
                                DateTime endDate;

                                if (DateTime.TryParse(row.Field<object>("Hora Inicio" + i).ToString(), out startDate) && DateTime.TryParse(row.Field<object>("Hora Fin" + i).ToString(), out endDate))
                                {
                                    schedule.StartsAt = startDate;
                                    schedule.EndsAt = endDate;
                                }

                                schedule.Classroom = new Classroom()
                                {
                                    ClassroomLocation = row.Field<object>("Edificio" + i) != null ? row.Field<object>("Edificio" + i).ToString() : "",
                                    ClassroomNumber = row.Field<object>("Salón" + i) != null ? row.Field<object>("Salón" + i).ToString() : ""
                                };

                                schedules.Add(schedule);
                            }
                        }
                    }
                    return schedules;
                };

                //Lambda function: Get all course teachers
                QueryCourseTeachers qct = (row) =>
                {
                    List<Teacher> teachers = new List<Teacher>();
                    for (int i = 1; i <= int.Parse(row.Field<double>("# Profesores").ToString()); i++)
                    {
                        if (row.Field<string>("Nómina" + i) != null && row.Field<string>("Nómina" + i).Trim() != "")
                        {
                            Teacher teacher = new Teacher()
                            {
                                Name = row.Field<string>("Nombre" + i) + " " + row.Field<string>("APaterno" + i) + " " + row.Field<string>("Amaterno" + i),
                                SchoolId = row.Field<string>("Nómina" + i)
                            };
                            teachers.Add(teacher);
                        }
                    }
                    return teachers;
                };

                //Query excel worksheet for all courses
                var coursesQuery = table
                    .Where(
                        x =>
                            x.Field<string>("Materia") != null
                         && x.Field<string>("Materia").Trim() != string.Empty
                         && x.Field<double>("Curso").ToString() != null
                         && x.Field<double>("Curso").ToString().Trim() != string.Empty
                         && x.Field<string>("Nombre de la Materia") != null
                         && x.Field<string>("Nombre de la Materia").Trim() != string.Empty
                    )
                    .Select(
                        x => new Course()
                        {
                            CourseCode = x.Field<string>("Materia"),
                            CourseNumber = x.Field<double>("Curso").ToString(),
                            CourseName = x.Field<string>("Nombre de la Materia"),
                            IsActive = x.Field<string>("Estado").ToUpper() == "ACTIVO",
                            Teachers = qct(x),
                            Schedules = qcs(x)
                        }
                    );

                List<Teacher> allTeachers = new List<Teacher>();
                List<Classroom> allClassrooms = new List<Classroom>();

                foreach (Course course in (IEnumerable<Course>)coursesQuery)
                {
                    allTeachers.AddRange(course.Teachers);

                    foreach (Schedule schedule in course.Schedules)
                    {
                        allClassrooms.Add(schedule.Classroom);
                    }
                }

                allTeachers = allTeachers.Distinct<Teacher>().ToList<Teacher>();
                allClassrooms = allClassrooms.Distinct<Classroom>().ToList<Classroom>();

                _UploadDAO.DeleteUploadedData(userId);

                _Logger.Info("BORRO DATOS DE BD");

                foreach (Teacher teacher in allTeachers)
                {
                    try
                    {
                        if (teacher.Name != null && teacher.Name != String.Empty
                           && teacher.SchoolId != null && teacher.SchoolId.Trim() != String.Empty)
                        {
                            _TeacherDAO.SaveTeacher(null, teacher.Name, teacher.SchoolId, userId);
                        }
                    }
                    catch (SISCONException ex)
                    {
                        continue;
                    }
                }

                foreach (Classroom classroom in allClassrooms)
                {
                    try
                    {
                        if (classroom.ClassroomLocation != null && classroom.ClassroomLocation.Trim() != String.Empty
                           && classroom.ClassroomNumber != null && classroom.ClassroomNumber.Trim() != String.Empty)
                        {
                            _ClassroomDAO.SaveClassroom(null, classroom.ClassroomLocation, classroom.ClassroomNumber, userId);
                        }
                    }
                    catch (SISCONException ex)
                    {
                        continue;
                    }
                }

                foreach (Course course in (IEnumerable<Course>)coursesQuery)
                {
                    long courseId = _CourseDAO.SaveCourse(null, course.CourseCode, course.CourseNumber, course.CourseName, course.IsActive, userId);

                    foreach (Schedule schedule in course.Schedules)
                    {
                        if (schedule.Days != null && schedule.StartsAt != null && schedule.EndsAt != null && schedule.Classroom != null)
                        {
                            long scheduleId = _ScheduleDAO.SaveSchedule(null, schedule.Days, schedule.StartsAt, schedule.EndsAt, schedule.Classroom.ClassroomLocation, schedule.Classroom.ClassroomNumber, userId);
                            _ScheduleDAO.SaveCourseSchedule(courseId, scheduleId);
                        }
                    }

                    foreach (Teacher teacher in course.Teachers)
                    {
                        if (teacher.SchoolId != null)
                        {
                            _TeacherDAO.SaveCourseTeacher(courseId, teacher.SchoolId);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (!(ex is SISCONException))
                {
                   
                }
                _Logger.Error("Error in UploadExcelFile()", ex);
                throw ex;
            }
            finally
            {
                
            }
        }

        public List<string> ListSheetInExcel(string filePath)
        {
            List<string> listSheet = new List<string>();
            OleDbConnectionStringBuilder sbConnection = new OleDbConnectionStringBuilder();
            String strExtendedProperties = String.Empty;
            sbConnection.DataSource = filePath;
            if (Path.GetExtension(filePath).Equals(".xls"))//for 97-03 Excel file
            {
                sbConnection.Provider = "Microsoft.Jet.OLEDB.4.0";
                strExtendedProperties = "Excel 8.0;HDR=Yes;IMEX=1";//HDR=ColumnHeader,IMEX=InterMixed
            }
            else if (Path.GetExtension(filePath).Equals(".xlsx"))  //for 2007 Excel file
            {
                sbConnection.Provider = "Microsoft.ACE.OLEDB.12.0";
                strExtendedProperties = "Excel 12.0;HDR=Yes;IMEX=1";
            }
            sbConnection.Add("Extended Properties", strExtendedProperties);
            using (OleDbConnection conn = new OleDbConnection(sbConnection.ToString()))
            {
                conn.Open();
                System.Data.DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    if (drSheet["TABLE_NAME"].ToString().Contains("$"))//checks whether row contains '_xlnm#_FilterDatabase' or sheet name(i.e. sheet name always ends with $ sign)
                    {
                        listSheet.Add(drSheet["TABLE_NAME"].ToString());
                    }
                }
            }
            return listSheet;
        }
    }
}